/*
 * @(#)StaffRecord.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.staff.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ApplicationExceptionList;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemParameterConstant;
import com.dcivision.framework.SystemParameterFactory;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.staff.bean.StaffRecord;

/**
  StaffRecordDAObject.java

  This class is the data access bean for table "STAFF_RECORD".

  @author      Wong Yam Lee
  @company     DCIVision Limited
  @creation date   05/08/2003
  @version     $Revision: 1.34 $
*/

public class StaffRecordDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.34 $";

  public static final String TABLE_NAME = "STAFF_RECORD";

  public StaffRecordDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }

  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("STAFF_CODE");
    this.vecDBColumn.add("ID_NO");
    this.vecDBColumn.add("PASSPORT_NO");
    this.vecDBColumn.add("ISSUE_COUNTRY_ID");
    this.vecDBColumn.add("SALUTATION_ID");
    this.vecDBColumn.add("FIRST_NAME_ENG");
    this.vecDBColumn.add("LAST_NAME_ENG");
    this.vecDBColumn.add("FIRST_NAME");
    this.vecDBColumn.add("LAST_NAME");
    this.vecDBColumn.add("FULL_NAME");
    this.vecDBColumn.add("GENDER");
    this.vecDBColumn.add("BIRTH_DATE");
    this.vecDBColumn.add("TITLE");
    this.vecDBColumn.add("AREA_CODE");
    this.vecDBColumn.add("PHONE_NO");
    this.vecDBColumn.add("MOBILE_NO");
    this.vecDBColumn.add("FAX_NO");
    this.vecDBColumn.add("PAGER_NO");
    this.vecDBColumn.add("EXTENSION");
    this.vecDBColumn.add("EMAIL");
    this.vecDBColumn.add("RENTAL_ADDRESS");
    this.vecDBColumn.add("MAIL_ADDRESS");
    this.vecDBColumn.add("REPORT_TO_STAFF_RECORD_ID");
    this.vecDBColumn.add("LEVEL_1_ID");
    this.vecDBColumn.add("LEVEL_2_ID");
    this.vecDBColumn.add("LEVEL_3_ID");
    this.vecDBColumn.add("LEVEL_4_ID");
    this.vecDBColumn.add("LEVEL_5_ID");
    this.vecDBColumn.add("LEVEL_6_ID");
    this.vecDBColumn.add("LEVEL_7_ID");
    this.vecDBColumn.add("LEVEL_8_ID");
    this.vecDBColumn.add("LEVEL_9_ID");
    this.vecDBColumn.add("LEVEL_10_ID");
    this.vecDBColumn.add("STATUS");
    this.vecDBColumn.add("SPOKEN_LANGUAGE");
    this.vecDBColumn.add("EXPERTISE");
    this.vecDBColumn.add("ALT_CONTACT_STAFF_RECORD_ID");
    this.vecDBColumn.add("REMARKS");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
  }

  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          return(tmpStaffRecord);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  public synchronized AbstractBaseObject getObjectByStaffName(String firstName, String lastName) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A ");
        sqlStat.append("WHERE  A.FIRST_NAME = ? AND A.LAST_NAME=? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, firstName);
        this.setPrepareStatement(preStat, 2, lastName);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          return(tmpStaffRecord);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append(this.getIDFilterWhereClause("A"));

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
                                        String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt<pageSize) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          tmpStaffRecord.setRecordCount(totalNumOfRecord);
          tmpStaffRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpStaffRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }

  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append(this.getIDFilterWhereClause("A"));
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          result.add(tmpStaffRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
    ApplicationExceptionList listAppEx = new ApplicationExceptionList();
    StaffRecord staffRecord = (StaffRecord)obj;
    PreparedStatement pstat = null;
    ResultSet rs = null;

    if (!Utility.isEmpty(staffRecord.getStaffCode()) && !"N/A".equals(staffRecord.getStaffCode())) {
      // Check duplicate staff code
      StringBuffer sbSqlStat = new StringBuffer();
      sbSqlStat.append("SELECT COUNT(*) ");
      sbSqlStat.append("FROM   STAFF_RECORD ");
      sbSqlStat.append("WHERE  STAFF_CODE = ? AND RECORD_STATUS = ? ");

      try {
        pstat = this.dbConn.prepareStatement(sbSqlStat.toString());
        this.setPrepareStatement(pstat, 1, staffRecord.getStaffCode());
        this.setPrepareStatement(pstat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = pstat.executeQuery();
        if (rs.next() && rs.getInt(1)>0) {
          listAppEx.addException(new ApplicationException(com.dcivision.staff.StaffErrorConstant.DUPLICATE_STAFF_CODE));
        }
      } catch (java.sql.SQLException sqle) {
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstat.close(); } catch (Exception ignore) {} finally { pstat = null; }
      }
    }

    if ( !Utility.isEmpty(staffRecord.getIDNo()) ) {
      // Check duplicate ID no
      StringBuffer sbSqlStat = new StringBuffer();
      sbSqlStat.append("SELECT COUNT(*) ");
      sbSqlStat.append("FROM   STAFF_RECORD ");
      sbSqlStat.append("WHERE  ID_NO = ? AND RECORD_STATUS = ? ");

      try {
        pstat = this.dbConn.prepareStatement(sbSqlStat.toString());
        this.setPrepareStatement(pstat, 1, staffRecord.getIDNo());
        this.setPrepareStatement(pstat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = pstat.executeQuery();
        if (rs.next() && rs.getInt(1)>0) {
          listAppEx.addException(new ApplicationException(com.dcivision.staff.StaffErrorConstant.DUPLICATE_ID_NO));
        }
      } catch (java.sql.SQLException sqle) {
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstat.close(); } catch (Exception ignore) {} finally { pstat = null; }
      }
    }

    listAppEx.throwException();
  }

  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
    ApplicationExceptionList listAppEx = new ApplicationExceptionList();
    StaffRecord staffRecord = (StaffRecord)obj;
    PreparedStatement pstat = null;
    ResultSet rs = null;

    // not allow a staff to report to himself
    if ( staffRecord.getID().equals(staffRecord.getReportToStaffRecordID()) ) {
      throw new ApplicationException(com.dcivision.staff.StaffErrorConstant.SELF_REPORT_TO);
    }
    // alternative contact person and the staff should not be the same person
    if ( staffRecord.getID().equals(staffRecord.getAlternativeContactStaffRecordID()) ) {
      throw new ApplicationException(com.dcivision.staff.StaffErrorConstant.SELF_ALTERNATIVE_CONTACT);
    }

    if (!Utility.isEmpty(staffRecord.getStaffCode()) && !"N/A".equals(staffRecord.getStaffCode())) {
      // Check duplicate staff name
      StringBuffer sbSqlStat = new StringBuffer();
      sbSqlStat.append("SELECT COUNT(*) ");
      sbSqlStat.append("FROM   STAFF_RECORD ");
      sbSqlStat.append("WHERE  STAFF_CODE = ? AND ID<>? AND RECORD_STATUS = ? ");

      try {
        pstat = this.dbConn.prepareStatement(sbSqlStat.toString());
        this.setPrepareStatement(pstat, 1, staffRecord.getStaffCode());
        this.setPrepareStatement(pstat, 2, staffRecord.getID());
        this.setPrepareStatement(pstat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = pstat.executeQuery();
        if (rs.next() && rs.getInt(1)>0) {
          throw new ApplicationException(com.dcivision.staff.StaffErrorConstant.DUPLICATE_STAFF_CODE);
        }
      } catch (java.sql.SQLException sqle) {
        listAppEx.addException(new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString()));
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstat.close(); } catch (Exception ignore) {} finally { pstat = null; }
      }
    }

    if ( !Utility.isEmpty(staffRecord.getIDNo()) ) {
      // Check duplicate ID no
      StringBuffer sbSqlStat = new StringBuffer();
      sbSqlStat.append("SELECT COUNT(*) ");
      sbSqlStat.append("FROM   STAFF_RECORD ");
      sbSqlStat.append("WHERE  ID_NO = ? AND ID<>? AND RECORD_STATUS = ? ");

      try {
        pstat = this.dbConn.prepareStatement(sbSqlStat.toString());
        this.setPrepareStatement(pstat, 1, staffRecord.getIDNo());
        this.setPrepareStatement(pstat, 2, staffRecord.getID());
        this.setPrepareStatement(pstat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = pstat.executeQuery();
        if (rs.next() && rs.getInt(1)>0) {
          listAppEx.addException(new ApplicationException(com.dcivision.staff.StaffErrorConstant.DUPLICATE_ID_NO));
        }
      } catch (java.sql.SQLException sqle) {
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstat.close(); } catch (Exception ignore) {} finally { pstat = null; }
      }
    }

    listAppEx.throwException();
  }

  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }

  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    StaffRecord tmpStaffRecord = (StaffRecord)((StaffRecord)obj).clone();

    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   STAFF_RECORD(ID, STAFF_CODE, ID_NO, PASSPORT_NO, ISSUE_COUNTRY_ID, SALUTATION_ID, FIRST_NAME_ENG, LAST_NAME_ENG, FIRST_NAME, LAST_NAME, FULL_NAME, GENDER, BIRTH_DATE, TITLE, AREA_CODE, PHONE_NO, MOBILE_NO, FAX_NO, PAGER_NO, EXTENSION, EMAIL, RENTAL_ADDRESS, MAIL_ADDRESS, REPORT_TO_STAFF_RECORD_ID, LEVEL_1_ID, LEVEL_2_ID, LEVEL_3_ID, LEVEL_4_ID, LEVEL_5_ID, LEVEL_6_ID, LEVEL_7_ID, LEVEL_8_ID, LEVEL_9_ID, LEVEL_10_ID, STATUS, SPOKEN_LANGUAGE, EXPERTISE, ALT_CONTACT_STAFF_RECORD_ID, REMARKS, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpStaffRecord.getStaffCode());
        setPrepareStatement(preStat, 3, tmpStaffRecord.getIDNo());
        setPrepareStatement(preStat, 4, tmpStaffRecord.getPassportNo());
        setPrepareStatement(preStat, 5, tmpStaffRecord.getIssueCountryID());
        setPrepareStatement(preStat, 6, tmpStaffRecord.getSalutationID());
        setPrepareStatement(preStat, 7, tmpStaffRecord.getFirstNameEng());
        setPrepareStatement(preStat, 8, tmpStaffRecord.getLastNameEng());
        setPrepareStatement(preStat, 9, tmpStaffRecord.getFirstName());
        setPrepareStatement(preStat, 10, tmpStaffRecord.getLastName());
        setPrepareStatement(preStat, 11, tmpStaffRecord.getFullName());
        setPrepareStatement(preStat, 12, tmpStaffRecord.getGender());
        setPrepareStatement(preStat, 13, tmpStaffRecord.getBirthDate());
        setPrepareStatement(preStat, 14, tmpStaffRecord.getTitle());
        setPrepareStatement(preStat, 15, tmpStaffRecord.getAreaCode());
        setPrepareStatement(preStat, 16, tmpStaffRecord.getPhoneNo());
        setPrepareStatement(preStat, 17, tmpStaffRecord.getMobileNo());
        setPrepareStatement(preStat, 18, tmpStaffRecord.getFaxNo());
        setPrepareStatement(preStat, 19, tmpStaffRecord.getPagerNo());
        setPrepareStatement(preStat, 20, tmpStaffRecord.getExtension());
        setPrepareStatement(preStat, 21, tmpStaffRecord.getEmail());
        setPrepareStatement(preStat, 22, tmpStaffRecord.getRentalAddress());
        setPrepareStatement(preStat, 23, tmpStaffRecord.getMailAddress());
        setPrepareStatement(preStat, 24, tmpStaffRecord.getReportToStaffRecordID());
        setPrepareStatement(preStat, 25, tmpStaffRecord.getLevel1ID());
        setPrepareStatement(preStat, 26, tmpStaffRecord.getLevel2ID());
        setPrepareStatement(preStat, 27, tmpStaffRecord.getLevel3ID());
        setPrepareStatement(preStat, 28, tmpStaffRecord.getLevel4ID());
        setPrepareStatement(preStat, 29, tmpStaffRecord.getLevel5ID());
        setPrepareStatement(preStat, 30, tmpStaffRecord.getLevel6ID());
        setPrepareStatement(preStat, 31, tmpStaffRecord.getLevel7ID());
        setPrepareStatement(preStat, 32, tmpStaffRecord.getLevel8ID());
        setPrepareStatement(preStat, 33, tmpStaffRecord.getLevel9ID());
        setPrepareStatement(preStat, 34, tmpStaffRecord.getLevel10ID());
        setPrepareStatement(preStat, 35, tmpStaffRecord.getStatus());
        setPrepareStatement(preStat, 36, tmpStaffRecord.getSpokenLanguage());
        setPrepareStatement(preStat, 37, tmpStaffRecord.getExpertise());
        setPrepareStatement(preStat, 38, tmpStaffRecord.getAlternativeContactStaffRecordID());
        setPrepareStatement(preStat, 39, tmpStaffRecord.getRemarks());
        setPrepareStatement(preStat, 40, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 41, new Integer(0));
        setPrepareStatement(preStat, 42, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 43, currTime);
        setPrepareStatement(preStat, 44, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 45, currTime);
        preStat.executeUpdate();
        tmpStaffRecord.setID(nextID);
        tmpStaffRecord.setCreatorID(sessionContainer.getUserRecordID());
        tmpStaffRecord.setCreateDate(currTime);
        tmpStaffRecord.setUpdaterID(sessionContainer.getUserRecordID());
        tmpStaffRecord.setUpdateDate(currTime);
        tmpStaffRecord.setUpdateCount(new Integer(0));
        tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
        tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
        return(tmpStaffRecord);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  public synchronized AbstractBaseObject getObjectIgnoreStatusByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A ");
        sqlStat.append("WHERE  A.ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        rs = preStat.executeQuery();
        if (rs.next()) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          return(tmpStaffRecord);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized AbstractBaseObject insertByFile(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    StaffRecord tmpStaffRecord = (StaffRecord)((StaffRecord)obj).clone();

    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   STAFF_RECORD(ID,   SALUTATION_ID,  FIRST_NAME, LAST_NAME, FULL_NAME,EMAIL, GENDER,   STATUS, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, STAFF_CODE  ) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ,?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpStaffRecord.getSalutationID());
        setPrepareStatement(preStat, 3, tmpStaffRecord.getFirstName());
        setPrepareStatement(preStat, 4, tmpStaffRecord.getLastName());
        setPrepareStatement(preStat, 5, tmpStaffRecord.getFullName());
        setPrepareStatement(preStat, 6, tmpStaffRecord.getEmail());
        setPrepareStatement(preStat, 7, tmpStaffRecord.getGender());
        setPrepareStatement(preStat, 8, tmpStaffRecord.getStatus());
        setPrepareStatement(preStat, 9, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 10, new Integer(0));
        setPrepareStatement(preStat, 11, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 12, currTime);
        setPrepareStatement(preStat, 13, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 14, currTime);
        setPrepareStatement(preStat, 15, tmpStaffRecord.getStaffCode());
        preStat.executeUpdate();
        tmpStaffRecord.setID(nextID);
        tmpStaffRecord.setCreatorID(sessionContainer.getUserRecordID());
        tmpStaffRecord.setCreateDate(currTime);
        tmpStaffRecord.setUpdaterID(sessionContainer.getUserRecordID());
        tmpStaffRecord.setUpdateDate(currTime);
        tmpStaffRecord.setUpdateCount(new Integer(0));
        tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
        tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
        return(tmpStaffRecord);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  public  synchronized AbstractBaseObject updateByFile(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    StaffRecord tmpStaffRecord = (StaffRecord)((StaffRecord)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE STAFF_RECORD ");
        sqlStat.append("SET      FIRST_NAME=?, LAST_NAME=?, FULL_NAME=? ,EMAIL=?, STATUS=?, UPDATE_COUNT=UPDATE_COUNT+1, UPDATER_ID=?, UPDATE_DATE=?,STAFF_CODE=? ");
        sqlStat.append("WHERE  ID=?     ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        setPrepareStatement(preStat, 1, tmpStaffRecord.getFirstName());
        setPrepareStatement(preStat, 2, tmpStaffRecord.getLastName());
        setPrepareStatement(preStat, 3, tmpStaffRecord.getFullName());
        setPrepareStatement(preStat, 4, tmpStaffRecord.getEmail());
        setPrepareStatement(preStat, 5, tmpStaffRecord.getStatus());
        setPrepareStatement(preStat, 6, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 7, currTime);
        setPrepareStatement(preStat, 8, tmpStaffRecord.getStaffCode());
        setPrepareStatement(preStat, 9, tmpStaffRecord.getID());

        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpStaffRecord.setUpdaterID(sessionContainer.getUserRecordID());
          tmpStaffRecord.setUpdateDate(currTime);
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          return(tmpStaffRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    StaffRecord tmpStaffRecord = (StaffRecord)((StaffRecord)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE STAFF_RECORD ");
        sqlStat.append("SET  STAFF_CODE=?, ID_NO=?, PASSPORT_NO=?, ISSUE_COUNTRY_ID=?, SALUTATION_ID=?, FIRST_NAME_ENG=?, LAST_NAME_ENG=?, FIRST_NAME=?, LAST_NAME=?, FULL_NAME=?, GENDER=?, BIRTH_DATE=?, TITLE=?, AREA_CODE=?, PHONE_NO=?, MOBILE_NO=?, FAX_NO=?, PAGER_NO=?, EXTENSION=?, EMAIL=?, RENTAL_ADDRESS=?, MAIL_ADDRESS=?, REPORT_TO_STAFF_RECORD_ID=?, LEVEL_1_ID=?, LEVEL_2_ID=?, LEVEL_3_ID=?, LEVEL_4_ID=?, LEVEL_5_ID=?, LEVEL_6_ID=?, LEVEL_7_ID=?, LEVEL_8_ID=?, LEVEL_9_ID=?, LEVEL_10_ID=?, STATUS=?, SPOKEN_LANGUAGE=?, EXPERTISE=?, ALT_CONTACT_STAFF_RECORD_ID=?, REMARKS=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=? ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpStaffRecord.getStaffCode());
        setPrepareStatement(preStat, 2, tmpStaffRecord.getIDNo());
        setPrepareStatement(preStat, 3, tmpStaffRecord.getPassportNo());
        setPrepareStatement(preStat, 4, tmpStaffRecord.getIssueCountryID());
        setPrepareStatement(preStat, 5, tmpStaffRecord.getSalutationID());
        setPrepareStatement(preStat, 6, tmpStaffRecord.getFirstNameEng());
        setPrepareStatement(preStat, 7, tmpStaffRecord.getLastNameEng());
        setPrepareStatement(preStat, 8, tmpStaffRecord.getFirstName());
        setPrepareStatement(preStat, 9, tmpStaffRecord.getLastName());
        setPrepareStatement(preStat, 10, tmpStaffRecord.getFullName());
        setPrepareStatement(preStat, 11, tmpStaffRecord.getGender());
        setPrepareStatement(preStat, 12, tmpStaffRecord.getBirthDate());
        setPrepareStatement(preStat, 13, tmpStaffRecord.getTitle());
        setPrepareStatement(preStat, 14, tmpStaffRecord.getAreaCode());
        setPrepareStatement(preStat, 15, tmpStaffRecord.getPhoneNo());
        setPrepareStatement(preStat, 16, tmpStaffRecord.getMobileNo());
        setPrepareStatement(preStat, 17, tmpStaffRecord.getFaxNo());
        setPrepareStatement(preStat, 18, tmpStaffRecord.getPagerNo());
        setPrepareStatement(preStat, 19, tmpStaffRecord.getExtension());
        setPrepareStatement(preStat, 20, tmpStaffRecord.getEmail());
        setPrepareStatement(preStat, 21, tmpStaffRecord.getRentalAddress());
        setPrepareStatement(preStat, 22, tmpStaffRecord.getMailAddress());
        setPrepareStatement(preStat, 23, tmpStaffRecord.getReportToStaffRecordID());
        setPrepareStatement(preStat, 24, tmpStaffRecord.getLevel1ID());
        setPrepareStatement(preStat, 25, tmpStaffRecord.getLevel2ID());
        setPrepareStatement(preStat, 26, tmpStaffRecord.getLevel3ID());
        setPrepareStatement(preStat, 27, tmpStaffRecord.getLevel4ID());
        setPrepareStatement(preStat, 28, tmpStaffRecord.getLevel5ID());
        setPrepareStatement(preStat, 29, tmpStaffRecord.getLevel6ID());
        setPrepareStatement(preStat, 30, tmpStaffRecord.getLevel7ID());
        setPrepareStatement(preStat, 31, tmpStaffRecord.getLevel8ID());
        setPrepareStatement(preStat, 32, tmpStaffRecord.getLevel9ID());
        setPrepareStatement(preStat, 33, tmpStaffRecord.getLevel10ID());
        setPrepareStatement(preStat, 34, tmpStaffRecord.getStatus());
        setPrepareStatement(preStat, 35, tmpStaffRecord.getSpokenLanguage());
        setPrepareStatement(preStat, 36, tmpStaffRecord.getExpertise());
        setPrepareStatement(preStat, 37, tmpStaffRecord.getAlternativeContactStaffRecordID());
        setPrepareStatement(preStat, 38, tmpStaffRecord.getRemarks());
        setPrepareStatement(preStat, 39, new Integer(tmpStaffRecord.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, 40, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 41, currTime);
        setPrepareStatement(preStat, 42, tmpStaffRecord.getID());
        setPrepareStatement(preStat, 43, tmpStaffRecord.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpStaffRecord.setUpdaterID(sessionContainer.getUserRecordID());
          tmpStaffRecord.setUpdateDate(currTime);
          tmpStaffRecord.setUpdateCount(new Integer(tmpStaffRecord.getUpdateCount().intValue() + 1));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          return(tmpStaffRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    StaffRecord tmpStaffRecord = (StaffRecord)((StaffRecord)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   STAFF_RECORD ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpStaffRecord.getID());
        setPrepareStatement(preStat, 2, tmpStaffRecord.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpStaffRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    StaffRecord tmpStaffRecord = (StaffRecord)this.oldValue;

    if (tmpStaffRecord != null) {
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getStaffCode()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getIDNo()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getPassportNo()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getIssueCountryID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getSalutationID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getFirstNameEng()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLastNameEng()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getFirstName()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLastName()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getFullName()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getGender()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getBirthDate()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getTitle()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getAreaCode()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getPhoneNo()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getMobileNo()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getFaxNo()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getPagerNo()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getExtension()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getEmail()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getRentalAddress()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getMailAddress()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getReportToStaffRecordID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel1ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel2ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel3ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel4ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel5ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel6ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel7ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel8ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel9ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getLevel10ID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getStatus()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getSpokenLanguage()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getExpertise()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getAlternativeContactStaffRecordID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getRemarks()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpStaffRecord.getUpdateDate()));
    }

    tmpStaffRecord = (StaffRecord)obj;
    if (tmpStaffRecord != null) {
      newValues.add(toAuditTrailValue(tmpStaffRecord.getID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getStaffCode()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getIDNo()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getPassportNo()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getIssueCountryID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getSalutationID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getFirstNameEng()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLastNameEng()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getFirstName()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLastName()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getFullName()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getGender()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getBirthDate()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getTitle()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getAreaCode()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getPhoneNo()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getMobileNo()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getFaxNo()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getPagerNo()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getExtension()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getEmail()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getRentalAddress()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getMailAddress()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getReportToStaffRecordID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel1ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel2ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel3ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel4ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel5ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel6ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel7ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel8ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel9ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getLevel10ID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getStatus()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getSpokenLanguage()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getExpertise()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getAlternativeContactStaffRecordID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getRemarks()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpStaffRecord.getUpdateDate()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/

  /**
   *  set REPORT_TO_STAFF_RECORD_ID null if the staff record is deleted
   *
   *  @param      id                    the id of the deleted staff record
   *  @return     No return values
   *  @throws     ApplicationException
   */
  private synchronized void updateReportToStaffRecordID (Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("UPDATE STAFF_RECORD ");
        sqlStat.append("SET  REPORT_TO_STAFF_RECORD_ID=? ");
        sqlStat.append("WHERE  REPORT_TO_STAFF_RECORD_ID=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, (Integer)null);
        setPrepareStatement(preStat, 2, id);
        preStat.executeUpdate();

      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   *  set ALT_CONTACT_STAFF_RECORD_ID null if the staff record is deleted
   *
   *  @param      id                    the id of the deleted staff record
   *  @return     No return values
   *  @throws     ApplicationException
   */
  private synchronized void updateAlternativeContactStaffRecordID (Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("UPDATE STAFF_RECORD ");
        sqlStat.append("SET  ALT_CONTACT_STAFF_RECORD_ID=? ");
        sqlStat.append("WHERE  ALT_CONTACT_STAFF_RECORD_ID=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, (Integer)null);
        setPrepareStatement(preStat, 2, id);
        preStat.executeUpdate();

      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   *  inactivate the corresponding user record if the staff record is deleted
   *
   *  @param      id                    the id of the deleted staff record
   *  @return     No return values
   *  @throws     ApplicationException
   */
  private synchronized void updateUserRecord (Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("UPDATE USER_RECORD ");
        sqlStat.append("SET    STATUS=? ");
        sqlStat.append("WHERE  USER_LINK_ID=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, "I");
        setPrepareStatement(preStat, 2, id);
        preStat.executeUpdate();

      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * postDelete
   *
   * @param id    The staff record ID
   * @throws ApplicationException
   */
  protected void postDelete(Integer id) throws ApplicationException {
    updateReportToStaffRecordID(id);
    updateAlternativeContactStaffRecordID(id);
    //updateUserRecord(id);
  }
  
  public synchronized void deleteRelatedUserRecord(Integer staffid) throws ApplicationException {
    if (this.isSoftDeleteEnabled) {
      this.softDeleteRelatedUserRecord(staffid);
    }else {
      this.hardDeleteRelatedUserRecord(staffid);
    }
  }
  
  /**
   * Hard Delete Related User Records
   * @param id
   * @throws ApplicationException
   */
  private synchronized void hardDeleteRelatedUserRecord(Integer staffid) throws ApplicationException {
    
  }
  
  /**
   * Soft Delete Related User Records
   * @param id
   * @throws ApplicationException
   */
  private synchronized void softDeleteRelatedUserRecord(Integer staffid) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("UPDATE USER_RECORD ");
        sqlStat.append("SET    RECORD_STATUS=?, UPDATE_COUNT=UPDATE_COUNT+1, UPDATE_DATE=?, UPDATER_ID=? ");
        sqlStat.append("WHERE  USER_LINK_ID=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_INACTIVE);
        this.setPrepareStatement(preStat, 2, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStat, 3, sessionContainer==null ? new Integer(0) : sessionContainer.getUserRecordID());
        this.setPrepareStatement(preStat, 4, staffid);
        preStat.executeUpdate();

      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized AbstractBaseObject getObjectByUserRecordID(Integer userRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A, USER_RECORD B ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND B.USER_TYPE = ? AND B.ID = ? AND A.ID = B.USER_LINK_ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, com.dcivision.user.bean.UserRecord.USER_TYPE_STAFF);
        this.setPrepareStatement(preStat, 3, userRecordID);
        rs = preStat.executeQuery();
        if (rs.next()) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          return(tmpStaffRecord);
        } else {
          return(null);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized StaffRecord getStaffRecordByStaffRecordIDLevelLabelField(Integer id, String levelLabelField) throws ApplicationException {
     PreparedStatement preStat = null;
     ResultSet rs = null;
     StringBuffer sqlStat = new StringBuffer();

     synchronized(dbConn) {
       try {
         sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
         sqlStat.append("FROM   STAFF_RECORD A ");
         sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? AND A."+levelLabelField+" IS NULL ");
         preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
         this.setPrepareStatement(preStat, 1, id);
         this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
         rs = preStat.executeQuery();
         if (rs.next()) {
           StaffRecord tmpStaffRecord = new StaffRecord();
           tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
           tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
           tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
           tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
           tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
           tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
           tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
           tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
           tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
           tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
           tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
           tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
           tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
           tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
           tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
           tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
           tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
           tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
           tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
           tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
           tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
           tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
           tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
           tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
           tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
           tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
           tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
           tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
           tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
           tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
           tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
           tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
           tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
           tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
           tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
           tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
           tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
           tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
           tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
           tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
           tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
           tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
           tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
           tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
           tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
           tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
           tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
           return(tmpStaffRecord);
         } else {
           return null;
         }
       } catch (ApplicationException appEx) {
         throw appEx;
       } catch (SQLException sqle) {
         log.error(sqle, sqle);
         throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
       } catch (Exception e) {
         log.error(e, e);
         throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
       } finally {
         try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
         try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
       }
     }
   }

   /**
    * Get the StaffRecord List by LevelFieldValue and LevelFieldLabel
    *
    * @param levelFieldValue
    * @param levelFieldLabel
    * @return
    * @throws ApplicationException
    */
   public synchronized List getStaffRecordListByLevelFieldValueLevelFieldLabel(
    String labelFieldValue,
    String levelIndex)
    throws ApplicationException {
   PreparedStatement preStat = null;
   ResultSet rs = null;
   StringBuffer sqlStat = new StringBuffer();
   List result = new ArrayList();
   synchronized(dbConn) {
     try {
       sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
       sqlStat.append("FROM   STAFF_RECORD A ");
       sqlStat.append("WHERE   A.LEVEL_"+levelIndex+"_ID = ? AND A.RECORD_STATUS = ?");
       preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
       this.setPrepareStatement(preStat, 1, labelFieldValue);
       this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
       rs = preStat.executeQuery();
       while (rs.next()) {
         StaffRecord tmpStaffRecord = new StaffRecord();
         tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
         tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
         tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
         tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
         tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
         tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
         tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
         tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
         tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
         tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
         tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
         tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
         tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
         tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
         tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
         tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
         tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
         tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
         tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
         tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
         tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
         tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
         tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
         tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
         tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
         tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
         tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
         tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
         tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
         tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
         tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
         tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
         tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
         tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
         tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
         tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
         tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
         tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
         tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
         tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
         tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
         tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
         tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
         tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
         tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
         tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
         tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
         result.add(tmpStaffRecord);
       }
       return(result);
     } catch (ApplicationException appEx) {
       throw appEx;
     } catch (SQLException sqle) {
       log.error(sqle, sqle);
       throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
     } catch (Exception e) {
       log.error(e, e);
       throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
     } finally {
       try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
       try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
     }
   }
 }

 public synchronized List getStaffRecordListByLevelFieldValueNextLevelFieldNull(
      String labelFieldValue,
      String levelIndex)
      throws ApplicationException {
     PreparedStatement preStat = null;
     ResultSet rs = null;
     StringBuffer sqlStat = new StringBuffer();
     List result = new ArrayList();
     String nextLevelIndex = new Integer((new Integer(levelIndex).intValue()+1)).toString();
     synchronized(dbConn) {
       try {
         sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
         sqlStat.append("FROM   STAFF_RECORD A ");
//         sqlStat.append("WHERE   A.LEVEL_"+levelIndex+"_ID = ? AND A.LEVEL_"+nextLevelIndex+"_ID IS NULL AND A.RECORD_STATUS = ?");
         sqlStat.append("WHERE   A.LEVEL_"+levelIndex+"_ID = ? AND A.RECORD_STATUS = ? ");
         int MaxLevel = TextUtility.parseInteger(SystemParameterFactory.getSystemParameter(SystemParameterConstant.STAFF_HIERARCHY_LEVEL));
         if (TextUtility.parseInteger(levelIndex) < MaxLevel){
         	sqlStat.append("AND A.LEVEL_"+nextLevelIndex+"_ID IS NULL ");
         }
         
         preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
         this.setPrepareStatement(preStat, 1, labelFieldValue);
         this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
         rs = preStat.executeQuery();
         while (rs.next()) {
           StaffRecord tmpStaffRecord = new StaffRecord();
           tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
           tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
           tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
           tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
           tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
           tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
           tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
           tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
           tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
           tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
           tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
           tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
           tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
           tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
           tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
           tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
           tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
           tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
           tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
           tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
           tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
           tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
           tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
           tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
           tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
           tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
           tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
           tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
           tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
           tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
           tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
           tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
           tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
           tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
           tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
           tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
           tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
           tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
           tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
           tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
           tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
           tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
           tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
           tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
           tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
           tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
           tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
           result.add(tmpStaffRecord);
         }
         return(result);
       } catch (ApplicationException appEx) {
         throw appEx;
       } catch (SQLException sqle) {
         log.error(sqle, sqle);
         throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
       } catch (Exception e) {
         log.error(e, e);
         throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
       } finally {
         try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
         try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
       }
     }
   }

   /**
    * Get the StaffRecord List by staffCode
    *
    * @param staffCode
    * @return
    * @throws ApplicationException
    */
   public synchronized List getStaffRecordListByStaffCode(String staffCode)
    throws ApplicationException {
   PreparedStatement preStat = null;
   ResultSet rs = null;
   StringBuffer sqlStat = new StringBuffer();
   List result = new ArrayList();
   synchronized(dbConn) {
     try {
       sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
       sqlStat.append("FROM   STAFF_RECORD A ");
       sqlStat.append("WHERE   A.STAFF_CODE = ? AND A.RECORD_STATUS = ?");
       preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
       this.setPrepareStatement(preStat, 1, staffCode);
       this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
       rs = preStat.executeQuery();
       while (rs.next()) {
         StaffRecord tmpStaffRecord = new StaffRecord();
         tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
         tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
         tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
         tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
         tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
         tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
         tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
         tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
         tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
         tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
         tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
         tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
         tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
         tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
         tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
         tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
         tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
         tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
         tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
         tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
         tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
         tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
         tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
         tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
         tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
         tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
         tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
         tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
         tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
         tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
         tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
         tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
         tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
         tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
         tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
         tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
         tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
         tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
         tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
         tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
         tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
         tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
         tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
         tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
         tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
         tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
         tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
         result.add(tmpStaffRecord);
       }
       return(result);
     } catch (ApplicationException appEx) {
       throw appEx;
     } catch (SQLException sqle) {
       log.error(sqle, sqle);
       throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
     } catch (Exception e) {
       log.error(e, e);
       throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
     } finally {
       try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
       try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
     }
   }
 }

   public synchronized Integer getUserRecordIDByStaffCode(String staffCode) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT B.ID ");
        sqlStat.append("FROM   STAFF_RECORD A, USER_RECORD B ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND B.USER_TYPE = ? AND A.STAFF_CODE = ? AND A.ID = B.USER_LINK_ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, com.dcivision.user.bean.UserRecord.USER_TYPE_STAFF);
        this.setPrepareStatement(preStat, 3, staffCode);
        rs = preStat.executeQuery();
        if (rs.next()) {
          Integer userRecordID = new Integer(getResultSetString(rs, "ID"));
          return(userRecordID);
        } else {
          return(null);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

   /**
    * Get the StaffRecord List by report to staff record ID
    *
    * @param reportToStaffRecordID
    * @return
    * @throws ApplicationException
    */
   public synchronized List getStaffRecordListByReportToStaffRecordID(Integer reportToStaffRecordID)
    throws ApplicationException {
   PreparedStatement preStat = null;
   ResultSet rs = null;
   StringBuffer sqlStat = new StringBuffer();
   List result = new ArrayList();
   synchronized(dbConn) {
     try {
       sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
       sqlStat.append("FROM   STAFF_RECORD A ");
       sqlStat.append("WHERE   A.REPORT_TO_STAFF_RECORD_ID = ? AND A.RECORD_STATUS = ?");
       preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
       this.setPrepareStatement(preStat, 1, reportToStaffRecordID);
       this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
       rs = preStat.executeQuery();
       while (rs.next()) {
         StaffRecord tmpStaffRecord = new StaffRecord();
         tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
         tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
         tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
         tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
         tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
         tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
         tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
         tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
         tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
         tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
         tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
         tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
         tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
         tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
         tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
         tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
         tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
         tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
         tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
         tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
         tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
         tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
         tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
         tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
         tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
         tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
         tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
         tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
         tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
         tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
         tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
         tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
         tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
         tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
         tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
         tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
         tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
         tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
         tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
         tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
         tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
         tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
         tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
         tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
         tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
         tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
         tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
         result.add(tmpStaffRecord);
       }
       return(result);
     } catch (ApplicationException appEx) {
       throw appEx;
     } catch (SQLException sqle) {
       log.error(sqle, sqle);
       throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
     } catch (Exception e) {
       log.error(e, e);
       throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
     } finally {
       try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
       try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
     }
   }
 }

  public synchronized List getStaffRecordListByLevelFieldValueNextLevelFieldNull(AbstractSearchForm searchForm,
      String labelFieldValue,
      String levelIndex
            ) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    String nextLevelIndex = new Integer((new Integer(levelIndex).intValue()+1)).toString();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.STAFF_CODE, A.ID_NO, A.PASSPORT_NO, A.ISSUE_COUNTRY_ID, A.SALUTATION_ID, A.FIRST_NAME_ENG, A.LAST_NAME_ENG, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.GENDER, A.BIRTH_DATE, A.TITLE, A.AREA_CODE, A.PHONE_NO, A.MOBILE_NO, A.FAX_NO, A.PAGER_NO, A.EXTENSION, A.EMAIL, A.RENTAL_ADDRESS, A.MAIL_ADDRESS, A.REPORT_TO_STAFF_RECORD_ID, A.LEVEL_1_ID, A.LEVEL_2_ID, A.LEVEL_3_ID, A.LEVEL_4_ID, A.LEVEL_5_ID, A.LEVEL_6_ID, A.LEVEL_7_ID, A.LEVEL_8_ID, A.LEVEL_9_ID, A.LEVEL_10_ID, A.STATUS, A.SPOKEN_LANGUAGE, A.EXPERTISE, A.ALT_CONTACT_STAFF_RECORD_ID, A.REMARKS, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   STAFF_RECORD A ");
//        sqlStat.append("WHERE   A.LEVEL_"+levelIndex+"_ID = ? AND A.LEVEL_"+nextLevelIndex+"_ID IS NULL AND A.RECORD_STATUS = ? ");
        sqlStat.append("WHERE   A.LEVEL_"+levelIndex+"_ID = ? AND A.RECORD_STATUS = ? ");
        int MaxLevel = TextUtility.parseInteger(SystemParameterFactory.getSystemParameter(SystemParameterConstant.STAFF_HIERARCHY_LEVEL));
        if (TextUtility.parseInteger(levelIndex) < MaxLevel){
        	sqlStat.append("AND A.LEVEL_"+nextLevelIndex+"_ID IS NULL ");
        }
        
        sqlStat.append(this.getIDFilterWhereClause("A"));
        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, labelFieldValue);
        this.setPrepareStatement(preStatCnt, 2, GlobalConstant.RECORD_STATUS_ACTIVE);        
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 3, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, labelFieldValue);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);        
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 3, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt<pageSize) {
          StaffRecord tmpStaffRecord = new StaffRecord();
          tmpStaffRecord.setID(getResultSetInteger(rs, "ID"));
          tmpStaffRecord.setStaffCode(getResultSetString(rs, "STAFF_CODE"));
          tmpStaffRecord.setIDNo(getResultSetString(rs, "ID_NO"));
          tmpStaffRecord.setPassportNo(getResultSetString(rs, "PASSPORT_NO"));
          tmpStaffRecord.setIssueCountryID(getResultSetInteger(rs, "ISSUE_COUNTRY_ID"));
          tmpStaffRecord.setSalutationID(getResultSetInteger(rs, "SALUTATION_ID"));
          tmpStaffRecord.setFirstNameEng(getResultSetString(rs, "FIRST_NAME_ENG"));
          tmpStaffRecord.setLastNameEng(getResultSetString(rs, "LAST_NAME_ENG"));
          tmpStaffRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpStaffRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpStaffRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpStaffRecord.setGender(getResultSetString(rs, "GENDER"));
          tmpStaffRecord.setBirthDate(getResultSetTimestamp(rs, "BIRTH_DATE"));
          tmpStaffRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpStaffRecord.setAreaCode(getResultSetString(rs, "AREA_CODE"));
          tmpStaffRecord.setPhoneNo(getResultSetString(rs, "PHONE_NO"));
          tmpStaffRecord.setMobileNo(getResultSetString(rs, "MOBILE_NO"));
          tmpStaffRecord.setFaxNo(getResultSetString(rs, "FAX_NO"));
          tmpStaffRecord.setPagerNo(getResultSetString(rs, "PAGER_NO"));
          tmpStaffRecord.setExtension(getResultSetString(rs, "EXTENSION"));
          tmpStaffRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpStaffRecord.setRentalAddress(getResultSetString(rs, "RENTAL_ADDRESS"));
          tmpStaffRecord.setMailAddress(getResultSetString(rs, "MAIL_ADDRESS"));
          tmpStaffRecord.setReportToStaffRecordID(getResultSetInteger(rs, "REPORT_TO_STAFF_RECORD_ID"));
          tmpStaffRecord.setLevel1ID(getResultSetInteger(rs, "LEVEL_1_ID"));
          tmpStaffRecord.setLevel2ID(getResultSetInteger(rs, "LEVEL_2_ID"));
          tmpStaffRecord.setLevel3ID(getResultSetInteger(rs, "LEVEL_3_ID"));
          tmpStaffRecord.setLevel4ID(getResultSetInteger(rs, "LEVEL_4_ID"));
          tmpStaffRecord.setLevel5ID(getResultSetInteger(rs, "LEVEL_5_ID"));
          tmpStaffRecord.setLevel6ID(getResultSetInteger(rs, "LEVEL_6_ID"));
          tmpStaffRecord.setLevel7ID(getResultSetInteger(rs, "LEVEL_7_ID"));
          tmpStaffRecord.setLevel8ID(getResultSetInteger(rs, "LEVEL_8_ID"));
          tmpStaffRecord.setLevel9ID(getResultSetInteger(rs, "LEVEL_9_ID"));
          tmpStaffRecord.setLevel10ID(getResultSetInteger(rs, "LEVEL_10_ID"));
          tmpStaffRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpStaffRecord.setSpokenLanguage(getResultSetString(rs, "SPOKEN_LANGUAGE"));
          tmpStaffRecord.setExpertise(getResultSetString(rs, "EXPERTISE"));
          tmpStaffRecord.setAlternativeContactStaffRecordID(getResultSetInteger(rs, "ALT_CONTACT_STAFF_RECORD_ID"));
          tmpStaffRecord.setRemarks(getResultSetString(rs, "REMARKS"));
          tmpStaffRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpStaffRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpStaffRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpStaffRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpStaffRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpStaffRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpStaffRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpStaffRecord.getCreatorID()));
          tmpStaffRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpStaffRecord.getUpdaterID()));
          tmpStaffRecord.setRecordCount(totalNumOfRecord);
          tmpStaffRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpStaffRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }   
}
